﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HPSF;
using System;
using System.Windows.Forms;


namespace ReveiwWord
{
    class HandleExl
    {
        private ISheet sheet;
        private HSSFWorkbook hssfworkbook;
        public  HandleExl(String path)
        {
            if (File.Exists(@"Word.xls"))
            {
                //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
                //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added.
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                    file.Close();
                }
                sheet = hssfworkbook.GetSheet("NewWord");

            }
            else
            {
                MessageBox.Show("There is not Word.xls");
            }


        }
        public void CreatExlWord(string pathTxt)
        {
            if (!File.Exists(@"Word.xls"))
            {
                hssfworkbook = new HSSFWorkbook();

                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI Team";
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "NPOI SDK Example";
                hssfworkbook.DocumentSummaryInformation = dsi;
                hssfworkbook.SummaryInformation = si;

                sheet = hssfworkbook.CreateSheet("NewWord");

                hssfworkbook.CreateSheet("Sheet1");
                hssfworkbook.CreateSheet("Sheet2");
                UpdateFromTxtToExl(pathTxt);
                WriteToExl(hssfworkbook);
            }
            else
            {
                UpdateFromTxtToExl(pathTxt);
                WriteToExl(hssfworkbook);
            }


        }

        private void WriteToExl(HSSFWorkbook hssfworkbook)
        {
            //Write the stream data of workbook to the root directory
            using (FileStream file = new FileStream(@"Word.xls", FileMode.Create))
            {
                hssfworkbook.Write(file);
                file.Close();
            }
        }
        private void UpdateFromTxtToExl(String pathTxt)
        {
            StreamReader objReader = new StreamReader(pathTxt);
            string s = "";
            int i = 0;
            int j = 0;
            do
            {
                IRow row = sheet.CreateRow(i);
                do
                {
                    s = objReader.ReadLine();
                   row.CreateCell(j).SetCellValue(s);
                    if (s == null)
                    {
                        break;
                    }
                    j++;
                } while (!s.Equals("$1"));
                j = 0;
                i++;
            } while (s != null);
            objReader.Close();
        }

        private void NewInputFromTxtToExl(String path,ISheet sheet)
        {
            StreamReader objReader = new StreamReader(path);
            string s = "";
            string st = null;
            int i = 0;
            int j = 0;

            do
            {
                IRow row = sheet.CreateRow(i);
                do
                {
                    s = objReader.ReadLine();
                    if (s == null)
                    {
                        break;
                    }
                    if (s.StartsWith("#"))
                        st = st + s;
                    else
                    {
                        row.CreateCell(j).SetCellValue(s);
                        j++;
                    }
                } while (!s.Equals("$1"));                
                row.CreateCell(j).SetCellValue(st);
                st = null;
                j = 0;
                i++;
            } while (s != null);
            objReader.Close();
        }
        public String[] GetRowFormExl(int index)
        {
            String [] word= new String[3];
            for (int i = 0; i < sheet.LastRowNum; i++)
            {
                HSSFCell cell =(HSSFCell) sheet.GetRow(index).GetCell(i);
                if (cell.StringCellValue.StartsWith("$"))
                    break;
                else if(cell.StringCellValue.StartsWith("+"))
                {
                    word[0] = cell.StringCellValue;
                    word[0] = word[0].Substring(1);
                   
                }else if(cell.StringCellValue.StartsWith("#"))
                {
                    string st = null;
                    st = cell.StringCellValue;
                    word[1] = st + "\n" + "\n";

                }else if(cell.StringCellValue.StartsWith("&"))
                {
                    word[2] = cell.StringCellValue;
                    word[2] = "["+word[2].Substring(1)+"]";                
                }
            }
            return word;
        }

    }
}
